#coding:utf-8
### Author DeanChen  deanchen@juniper.net

import sys
import xlwt
import MySQLdb
import datetime
import time
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
from email.header import Header
import os.path
import subprocess

backup_file = 'Resources_' + time.strftime("%Y-%m-%d") + '.xls'
save_path_name = sys.path[0] + '/' + backup_file
print 'Excel saves at '+ save_path_name


def export():
    conn = MySQLdb.Connect(host = 'localhost', user = 'root', passwd = 'netscreen', db = 'regression', charset = 'utf8')
    cursor = conn.cursor()

    table_names_count = cursor.execute('show tables;')
    table_names = cursor.fetchall()
    
    workbook = xlwt.Workbook(encoding = 'utf-8')

    for table in table_names:
        select_table = 'select * from ' + str(table[0]) + ';'
        count = cursor.execute(select_table)
        print 'Get ' + str(count) + ' records in table ' + str(table[0]) + '!!!'                  

        if count > 0:
            ####reset the cursor
            cursor.scroll(0, mode = 'absolute')
            results = cursor.fetchall()
            fields = cursor.description
        
            #sheet = workbook.add_sheet(str(table[0]))
            sheet = workbook.add_sheet(str(table[0]), cell_overwrite_ok = True)
        
            ###write the fields part
            for field in range(0,len(fields)):
                sheet.write(0,field,fields[field][0])

            ###write the data info
            for row in range(1,len(results)+1):
                for col in range(0,len(fields)):
                    sheet.write(row,col,u'%s' %results[row-1][col])

        else:
            print 'No data in table ' + str(table[0])

    workbook.save(save_path_name)               
    cursor.close() 
    conn.close()

    ### send Email to me
    print 'Send Email to me ~~~'
    sender = 'pdt-res@englab.juniper.net'
    receivers = ['deanchen@juniper.net','tommysun@juniper.net']

    message = MIMEMultipart()
#    message['From'] = Header('pdt-res','utf-8')
#    message['To'] = Header('Device Manager','utf-8')
    subject = 'Backup the Database of pdt-res daily ~'
    message['Subject'] = Header(subject,'utf-8')

    message.attach(MIMEText('Attachement is the backup of database from pdt-res server. Save the Data incase the server crash ~','plain','utf-8'))
    
    att = MIMEText(open(backup_file,'rb').read(),'base64','utf-8')
    att["Content-Type"] = 'application/ms-excel'
    #att["Content-Type"] = 'application/octet-stream'
    att["Content-Disposition"] = "attachment;filename = %s" % backup_file 
    message.attach(att)

    try:
        smtpObj = smtplib.SMTP('localhost')
        smtpObj.sendmail(sender, receivers, message.as_string())
        print 'Email send successfully ~'

    except smtplib.SMTPException:
        print 'ERROR: Email send failed !!! Please double check !!!'


if __name__ == '__main__':
    export()
